package tur.br.moradadosol.dao.implementacao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.log4j.Logger;

import tur.br.moradadosol.dao.HospedeDAO;
import tur.br.moradadosol.model.Hospede;
import tur.br.moradadosol.util.Conf;
import tur.br.moradadosol.util.Constantes;
import tur.br.moradadosol.util.Util;

public class SQLHospedeDAO implements HospedeDAO {
	
	private Connection connection;
	
	public static Logger logger = Logger.getLogger(SQLHospedeDAO.class);
	
	public SQLHospedeDAO(Connection conn) {
		connection = conn;
		
		logger.setLevel(Constantes.LOGGER_LEVEL);
	}
	
	public void delete(int codigo) throws Exception {
		
		String sql = "delete from " + Conf.getProp("TABLE_HOSPEDE") + " where CD_HOSPEDE = ?";
		
		PreparedStatement pstm = null;
		
		try {
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, codigo);
			
			pstm.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Erro ao tentar deletar hospede", e);
			throw e;
		}  finally {
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
	}

	public Hospede getHospedeByCodigo(int codigo) throws Exception {
		
		PreparedStatement pstm = null;
		
		ResultSet rs = null;

		Hospede hospede = null;
		
		try {
			String sql = "select * from " + Conf.getProp("TABLE_HOSPEDE") + " where CD_HOSPEDE = ?";
			
			pstm = connection.prepareStatement(sql);
			
			pstm.setInt(1, codigo);
			
			rs = pstm.executeQuery();
			
			if(rs.next()) {
				
				hospede = getHospede(rs);
			}
			
		} catch (Exception e) {
			logger.error("Erro ao tentar obter hospede por codigo", e);
			throw e;
		} finally {

			try {
				rs.close();
				rs = null;
			} catch (Exception e) {}
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
		
		return hospede;
		
	}
	
	public Collection getHospedes() throws Exception {

		Collection hospedes = new ArrayList();
		
		PreparedStatement pstm = null;
		
		ResultSet rs = null;
		
		try {
			String sql = "select * from " + Conf.getProp("TABLE_HOSPEDE");
			
			pstm = connection.prepareStatement(sql);
			
			rs = pstm.executeQuery();
			rs.first();
			
			while(rs.next()) {
				hospedes.add(getHospede(rs));
			}
			
		} catch (Exception e) {
			logger.error("Erro ao tentar obter hospedes", e);
			throw e;
		} finally {

			try {
				rs.close();
				rs = null;
			} catch (Exception e) {}
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
		
		return hospedes;
	}

	public Collection getHospedesByNome(String nome) throws Exception {
		
		String sql = "select * from " + Conf.getProp("TABLE_HOSPEDE")
				+ " where NOME like '%" + nome + "%'";
		
		PreparedStatement pstm = null;
		
		ResultSet rs = null;

		Collection hospedes = new ArrayList();
		
		try {
			pstm = connection.prepareStatement(sql);
			
			rs = pstm.executeQuery();
			
			while(rs.next()) {
				hospedes.add(getHospede(rs));
			}
			
		} catch (Exception e) {
			logger.error("Erro ao tentar obter hospedes por nome", e);
			throw e;
		} finally {

			try {
				rs.close();
				rs = null;
			} catch (Exception e) {}
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
		
		return hospedes;
	}

	public void insert(Hospede hospede) throws Exception {
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			String sql = "insert into " + Conf.getProp("TABLE_HOSPEDE");
			sql += " (CD_HOSPEDE, ";
			sql += "NOME, ";
			sql += "ENDERECO, ";
			sql += "CIDADE, ";
			sql += "ESTADO, ";
			sql += "CEP, ";
			sql += "PAIS, ";
			sql += "BAIRRO, ";
			sql += "EMAIL, ";
			sql += "NASCIMENTO, ";
			sql += "TELEFONE, ";
			sql += "CELULAR, ";
			sql += "CARRO, ";
			sql += "PROFISSAO, ";
			sql += "FONE_COMERCIAL, ";
			sql += "ENDERECO_COMERCIAL, ";
			sql += "RG, ";
			sql += "CPF) ";
			sql += " values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			int index = 1;

			pstm = connection.prepareStatement(sql);

			pstm.setString(index++, hospede.getNome());
			pstm.setString(index++, hospede.getEndereco());
			pstm.setString(index++, hospede.getCidade());
			pstm.setInt(index++, hospede.getEstado());
			pstm.setInt(index++, hospede.getCep());
			pstm.setString(index++, hospede.getPais());
			pstm.setString(index++, hospede.getBairro());
			pstm.setString(index++, hospede.getEmail());
			pstm.setDate(index++, Util.getDataDate(hospede.getNascimento()));
			pstm.setString(index++, hospede.getTelefone());
			pstm.setString(index++, hospede.getCelular());
			pstm.setString(index++, hospede.getCarro());
			pstm.setString(index++, hospede.getProfissao());
			pstm.setString(index++, hospede.getFoneComercial());
			pstm.setString(index++, hospede.getEnderecoComercial());
			pstm.setString(index++, hospede.getRg());
			pstm.setString(index++, hospede.getCpf());
			
			pstm.executeUpdate();
			
			pstm = connection.prepareStatement(
					"select max(cd_hospede) as cd_hospede from " + Conf.getProp("TABLE_HOSPEDE"));
			
			rs = pstm.executeQuery();
			
			if(rs.next()) {
				
				hospede.setCodigo(rs.getInt("CD_HOSPEDE"));
			}
			
		} catch (SQLException e) {
			hospede.setCodigo(0);
			logger.error("Erro ao tentar inserir hospede", e);
			throw e;
		} finally {

			try {
				rs.close();
				rs = null;
			} catch (Exception e) {}
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
	}

	public void update(Hospede hospede) throws Exception {
		
		String sql = "update " + Conf.getProp("TABLE_HOSPEDE") + " set NOME = ?, "
			+ "NASCIMENTO = ?, ENDERECO = ?, BAIRRO = ?, CEP = ?, CIDADE = ?, "
			+ "PAIS = ?, ESTADO = ?, CARRO = ?, TELEFONE = ?, FONE_COMERCIAL = ?, "
			+ "CELULAR = ?, PROFISSAO = ?, ENDERECO_COMERCIAL = ?, CPF = ?, "
			+ "RG = ?, EMAIL = ? where CD_HOSPEDE = ?";
		
		int index = 1;
		
		PreparedStatement pstm = null;
		
		try {
			pstm = connection.prepareStatement(sql);
			
			pstm.setString(index++, hospede.getNome());
			pstm.setDate(index++, Util.getDataDate(hospede.getNascimento()));
			pstm.setString(index++, hospede.getEndereco());
			pstm.setString(index++, hospede.getBairro());
			pstm.setInt(index++, hospede.getCep());
			pstm.setString(index++, hospede.getCidade());
			pstm.setString(index++, hospede.getPais());
			pstm.setInt(index++, hospede.getEstado());
			pstm.setString(index++, hospede.getCarro());
			pstm.setString(index++, hospede.getTelefone());
			pstm.setString(index++, hospede.getFoneComercial());
			pstm.setString(index++, hospede.getCelular());
			pstm.setString(index++, hospede.getProfissao());
			pstm.setString(index++, hospede.getEnderecoComercial());
			pstm.setString(index++, hospede.getCpf());
			pstm.setString(index++, hospede.getRg());
			pstm.setString(index++, hospede.getEmail());
			pstm.setInt(index++, hospede.getCodigo());
			
			pstm.executeUpdate();
		} catch (SQLException e) {
			logger.error("Erro ao tentar atualizar hospede", e);
			throw e;
		} finally {
			
			try {
				pstm.close();
				pstm = null;
			} catch (Exception e) {}
		}
	}
	
	private Hospede getHospede(ResultSet rs) throws Exception {
		
		Hospede hospede = new Hospede();
			
		hospede.setCodigo(rs.getInt("CD_HOSPEDE"));
		hospede.setNome(rs.getString("NOME"));
		hospede.setNascimento(Util.getDataCalendar(rs.getDate("NASCIMENTO")));
		hospede.setEndereco(rs.getString("ENDERECO"));
		hospede.setBairro(rs.getString("BAIRRO"));
		hospede.setCep(rs.getInt("CEP"));
		hospede.setCidade(rs.getString("CIDADE"));
		hospede.setPais(rs.getString("PAIS"));
		hospede.setEstado(rs.getInt("ESTADO"));
		hospede.setCarro(rs.getString("CARRO"));
		hospede.setTelefone(rs.getString("TELEFONE"));
		hospede.setFoneComercial(rs.getString("FONE_COMERCIAL"));
		hospede.setCelular(rs.getString("CELULAR"));
		hospede.setProfissao(rs.getString("PROFISSAO"));
		hospede.setEnderecoComercial(rs.getString("ENDERECO_COMERCIAL"));
		hospede.setCpf(rs.getString("CPF"));
		hospede.setRg(rs.getString("RG"));
		hospede.setEmail(rs.getString("EMAIL"));
		
		return hospede;
	}

}
